package com.southgis.ibase.sql.func;

import com.southgis.ibase.sql.utils.SqlUtil;
import com.southgis.ibase.sql.enums.ColumnTypeEnum;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

public class PostgresqlFunc extends CommonFunc
{
	//标准写法
//	@Override
//	public String charType(int len)
//	{
//	}

	//标准写法
//	@Override
//	public String binaryType(int len)
//	{
//	}
	
	//标准写法
//	@Override
//	public String datetimeType()
//	{
//		return "timestamp";
//	}

	//可使用标准类型：double precision
//	@Override
//	public String doubleType()
//	{
//		return "float8";
//	}
	
	//标准写法
//	@Override
//	public String decimalType(int len,int scale)
//	{
//	}

	//标准写法
//	@Override
//	public String intType(int width)
//	{
//	}
	
	//标准写法
//	@Override
//	public String notEmptyCharWhere(String fieldName)
//	{
//	}

	//标准写法
//	@Override
//	public String emptyCharWhere(String fieldName)
//	{
//	}

	@Override
	public String concatChar(String... charVals)
	{
		return "(" + String.join(" || ", charVals)+")";
	}

	@Override
	public String getCharToDateInvoke(String charVal, int fmt)
	{
		String sql="";
		switch(fmt)
		{
		case 0: //yyyy-MM-dd HH:mm:ss
			sql="to_timestamp("+charVal+",'yyyy-mm-dd HH24:mi:ss')";
			break;
		case 1: //yyyyMMddHHmmss
			sql="to_timestamp("+charVal+",'yyyymmddHH24miss')";
			break;
		case 10: //yyyy-MM-dd
			sql="to_timestamp("+charVal+",'yyyy-mm-dd')";
			break;
		case 11: //yyyyMMdd
			sql="to_timestamp("+charVal+",'yyyymmdd')";
			break;
		default://默认格式，即不用格式串
			sql=getCastInvoke(charVal,java.sql.Types.TIMESTAMP,0,0);
			break;
		}

		return sql;
	}
	
	//标准写法
//	@Override
//	public String getYearInvoke(String dateVal)
//	{
//	}

	//标准写法
//	@Override
//	public String getDateConst(String fmtVal)
//	{
//	}

	//标准写法
//	@Override
//	public String getCastInvoke(String orgVal,int targetType,int length, int scale)
//	{
//	}
	
	//标准写法
//	@Override
//	public String pageSql(String sql, int offset, int pageSize)
//	{
//	}
	
	//标准写法
//	@Override
//	public String countSql(String sql)
//	{
//	}
	
	@Override
	public String sqlOfTableCount(String tableName, String schema)
	{
//		return "SELECT COUNT(*) AS " + IExclusiveDatabaseFunc.ResultColumns.COUNT
//				+ " FROM (select schemaname,tablename from pg_tables "
//				+ " union "
//				+ " select schemaname,viewname as tablename from pg_views) pg_tbl_tv "
//				+ " WHERE "
//				+ " schemaname = '" + schema.toLowerCase() + "' AND "
//				+ " tablename = '" + tableName + "'";
		return "SELECT COUNT(*) AS " + IExclusiveDatabaseFunc.ResultColumns.COUNT
				+ " FROM information_schema.tables "
				+ " WHERE "
				+ " lower(table_schema) = '" + schema.toLowerCase() + "' AND "
				+ " lower(table_name) = '" + tableName.toLowerCase() + "'";
	}
	
	@Override
	public String sqlOfListTable(String name, TableTypeEnum typeEnum, String schema)
	{
		StringBuilder sbSql=new StringBuilder();
		sbSql.append("select c.relname AS ").append(IExclusiveDatabaseFunc.ResultColumns.TABLE_NAME)
			.append(",cast(obj_description(c.relfilenode,'pg_class') as varchar) AS ").append(IExclusiveDatabaseFunc.ResultColumns.TABLE_COMMENT)
			.append(",CASE c.relkind WHEN 'v' THEN 2 ELSE 1 END AS ").append(IExclusiveDatabaseFunc.ResultColumns.TABLE_TYPE)
			.append(" FROM pg_class c left join pg_namespace n on c.relnamespace=n.oid");
		//添加该条件会导致有约束的表无法查询出来，因此注释掉
		// .append(" WHERE c.relchecks=0 ");
		sbSql.append(" WHERE lower(n.nspname) = '").append(schema.toLowerCase()).append("'");
		boolean needAnd=true;
		boolean needOr=false;
		final int type = typeEnum.getType();
		if(!StringUtils.isEmpty(name)) {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			sbSql.append(" lower(c.relname) ");
			if((type&0x100)==0x100)
				sbSql.append(" = ");
			else {
				sbSql.append(" like ");
				name=name.replace("_", "\\_");
			}
			sbSql.append("'").append(name.toLowerCase()).append("'");
		}
		if(type!=0) {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			
			needOr=false;
			sbSql.append(" (");
			if((type&1)==1) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" c.relkind='r' ");
			}
			if((type&2)==2) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" c.relkind='v' ");
			}
			sbSql.append(")");
		}
		return sbSql.toString();
	}

	//查询information_schema.columns视图也能得到列定义信息
	@Override
	public String sqlOfListColumns(String tableName, String schema)
	{
		//设置查询类型：0不加条件，1 用等于，-1 用like
		int queryType=0;
		if(!StringUtils.isEmpty(tableName)) {
			queryType=tableName.indexOf('%')<0?1:-1;
			if(queryType==-1) {
				tableName=tableName.replace("_", "\\_");
			}
		}
		return "SELECT c.relname as " + IExclusiveDatabaseFunc.ResultColumns.TABLE_NAME
				+ ",a.attname as " + IExclusiveDatabaseFunc.ResultColumns.COLUMN_NAME
				+ ",col_description(a.attrelid,a.attnum) as " + IExclusiveDatabaseFunc.ResultColumns.COLUMN_COMMENT
				+ ",CASE a.attnotnull WHEN 'f' THEN 'Y' ELSE 'N' END as " + IExclusiveDatabaseFunc.ResultColumns.COLUMN_NULLABLE
				+ ",ty.typname as " + IExclusiveDatabaseFunc.ResultColumns.DATA_TYPE
				+ ",substring(format_type(a.atttypid,a.atttypmod) from '\\((\\d+)') as " + IExclusiveDatabaseFunc.ResultColumns.DATA_LENGTH
				+ ",substring(format_type(a.atttypid,a.atttypmod) from '\\,(\\d+)\\)') as " + IExclusiveDatabaseFunc.ResultColumns.DATA_SCALE
				//提取(开始到)结束的内容：substring(format_type(a.atttypid,a.atttypmod) from '\(.*\)')
				+ ",format_type(a.atttypid,a.atttypmod) as " + IExclusiveDatabaseFunc.ResultColumns.DATA_DEFINE
				+ " FROM pg_attribute a left join pg_class c on a.attrelid=c.oid "
				+ " left join pg_namespace n on c.relnamespace=n.oid "
				+ " left join pg_type ty on a.atttypid=ty.oid "
				+ " WHERE a.attnum>0 and a.attisdropped=false " //也可用a.atttypid>0，等于0是一些无效字段
				+ " and lower(n.nspname)='" + schema.toLowerCase() + "' "
				+ (queryType==0?"":(" and lower(c.relname) "+(queryType==1?"=":" like ")+" '" + tableName.toLowerCase() + "' "));
	}

	@Override
	public String sqlOfListConstraint(ConstraintTypeEnum typeEnum, String tableName,String schema)
	{
		StringBuilder sbSql=new StringBuilder();
		sbSql.append("SELECT uc.conname as ").append(IExclusiveDatabaseFunc.ResultColumns.CONS_NAME)
				.append(",CASE uc.contype WHEN 'p' THEN 1 WHEN 'u' THEN 2 WHEN 'f' THEN 4 ELSE 0 END as ").append(IExclusiveDatabaseFunc.ResultColumns.CONS_TYPE)
				.append(",a.attname as ").append(IExclusiveDatabaseFunc.ResultColumns.COLUMN_NAME)
				.append(" FROM pg_constraint uc left join pg_class c on uc.conrelid=c.oid ")
				.append(" left join pg_attribute a on c.oid=a.attrelid and uc.conkey[1]=a.attnum ")
				.append(" left join pg_namespace n on c.relnamespace=n.oid ")
				.append(" WHERE lower(n.nspname)='").append(schema.toLowerCase()).append("' AND ")
				.append(" lower(c.relname)='").append(tableName.toLowerCase()).append("'");
		boolean needAnd=true;
		boolean needOr=false;
		final int type = typeEnum.getType();
		//p  u  f
		if(type!=0) {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			
			needOr=false;
			sbSql.append("(");
			if((type&1)==1) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.contype='p' "); //主键
			}
			if((type&2)==2) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.contype='u' "); //唯一
			}
			if((type&4)==4) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.contype='f' "); //外键
			}

			sbSql.append(")");
		}else {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			sbSql.append(" uc.contype<>'c' "); //检查
		}
		return sbSql.toString();
	}
	
	@Override
	public List<String> sqlOfCreateTable(String tableName, String comment, List<ColumnDefine> colDef, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		StringBuilder sql=new StringBuilder();
		List<String> pkCol=new ArrayList<String>(colDef.size());
		
		lsSql.add("");//添加一个占位元素，最后在生成完create语句后，替换
		String fullTable=getFullTable(schema,tableName);
		sql.append("CREATE TABLE ").append(fullTable).append("(\n");
		for(int inx=0;inx<colDef.size();++inx) {
			ColumnDefine def=colDef.get(inx);
			if(inx>0) sql.append(",\n");
			sql.append(def.getName()).append(" ").append(getColumnTypeString(def));
			if(!def.isNullable()) {
				sql.append(" NOT NULL");
			}
			
			if(def.isPrimaryKey()) {
				pkCol.add(def.getName());
			}
			if(!StringUtils.isEmpty(def.getComment())) {
				lsSql.add(addColumnComment(fullTable,def.getName(),def.getComment()));
			}
		}
		if(pkCol.size()>0) {
			sql.append(",\n");
			sql.append("PRIMARY KEY (");
			sql.append(String.join(",", pkCol));
			sql.append(")");
		}
		sql.append("\n)");
		lsSql.set(0, sql.toString());
		
		if(!StringUtils.isEmpty(comment)) {
			lsSql.add(sqlOfModifyTableComment(comment,tableName,schema));
		}
		
		return lsSql;
	}
	
	@Override
	public String sqlOfModifyTableComment(String comment,String tableName,String schema)
	{
		return "COMMENT ON TABLE "+getFullTable(schema,tableName)
				+" is '"+SqlUtil.toSql(comment)+"'";
	}

	@Override
	public List<String> sqlOfAddColumn(ColumnDefine colDef,String tableName, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		String sql;
		String fullTable=getFullTable(schema,tableName);
		sql="ALTER TABLE "+fullTable
				+" ADD COLUMN "+colDef.getName()+" "+getColumnTypeString(colDef)+(colDef.isNullable()?" ":" NOT NULL");
		lsSql.add(sql);
		
		if(!StringUtils.isEmpty(colDef.getComment())) {
			sql = addColumnComment(fullTable, colDef.getName(), colDef.getComment());
			lsSql.add(sql);
		}
		return lsSql;
	}
	protected String addColumnComment(String fullTable,String colName,String comment)
	{
		return "COMMENT ON COLUMN "+fullTable
				+"."+colName+" is '"+SqlUtil.toSql(comment)+"'";
	}
	
	@Override
	public List<String> sqlOfModifyColumn(ModifyColumnDefine colDef,String tableName, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		String sql;
		String fullTable=getFullTable(schema,tableName);
		if(colDef.isModifyName()) {
			sql="ALTER TABLE "+fullTable+" RENAME COLUMN "+colDef.getOldName()+" TO "+colDef.getName();
			lsSql.add(sql);
		}
		if(colDef.isModifyType()) {
			String toTypeStr=getColumnTypeString(colDef);
			sql="ALTER TABLE "+fullTable+" ALTER COLUMN "+colDef.getName()+" TYPE "+toTypeStr
				+ " USING " + colDef.getName()+"::"+toTypeStr; //加这一句，用于处理字段值的转换
			lsSql.add(sql);
		}
		if(colDef.isModifyNullable()) {
			sql="ALTER TABLE "+fullTable+" ALTER COLUMN "+colDef.getName()+" "+(colDef.isNullable()?" DROP NOT NULL":" SET NOT NULL");
			lsSql.add(sql);
		}
		if(colDef.isModifyComment()) {
			sql = addColumnComment(fullTable, colDef.getName(), colDef.getComment());
			lsSql.add(sql);
		}
		return lsSql;
	}
	
	//标准写法
//@Override
//public String sqlOfDropColumn(String colName, String tableName, String schema)
//{
//	return String.format("alter table %s drop column %s", getFullTable(schema,tableName),colName);
//}
	
	//标准写法
//	@Override
//	public String sqlOfDropPrimaryKey(String keyName, String tableName, String schema)
//	{
//		return "ALTER TABLE "+getFullTable(schema,tableName)+" drop CONSTRAINT "+keyName;
//	}
	
	//标准写法
//	@Override
//	public String sqlOfAddUniqueKey(String keyName, String colName, String tableName, String schema)
//	{
//	}

	//标准写法
//	@Override
//	public String sqlOfDropUniqueKey(String keyName, String tableName, String schema)
//	{
//	}

	//标准写法
//	@Override
//	public String sqlOfDropForeignKey(String keyName, String tableName, String schema)
//	{
//	}
	
	@Override
	public String sqlOfEnableForeignKey(String keyName, Boolean enabled, String tableName, String schema)
	{
		return "ALTER TABLE "+getFullTable(schema,tableName)
				+" "+(enabled?"enable":"disable")+" trigger all";
	}
	

	/**
	 * 重载父类的实现，针对特殊类型特殊处理
	 */
	@Override
	protected String getColumnTypeString(ColumnDefine colDef)
	{
		String typeStr=super.getColumnTypeString(colDef);
//		int len=colDef.getLength();
		if(colDef.getType()==ColumnTypeEnum.Json)
		{
			typeStr="json";
		}
//		else if(colDef.getType()==ColumnTypeEnum.Geometry) {
//			//定义	 point（点）,line（两点直线段）,polyline（折线）,polygon（多边形），mpolygon（空心多边形，第一部分为外边界，剩下每一块描述的是内部空心边界）,rectangle（矩形）,circle（圆）
//			switch(colDef.getSuftype())
//			{
//			case "point":
//				typeStr="point";
//				break;
//			case "line":
//				typeStr="lseg";
//				break;
//			case "polyline":
//				typeStr="path";
//				break;
//			case "polygon":
//				typeStr="polygon";
//				break;
//			case "rectangle":
//				typeStr="box";
//				break;
//			case "circle":
//				typeStr="circle";
//				break;
//			case "mpolygon":
//				if(len==0) len=4000;
//				typeStr=binaryType(len);
//				break;
//			default:
//				if(len==0) len=2000;
//				typeStr=binaryType(len);
//				break;
//			}
//		}
		return typeStr;
	}

}
